using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Data;
using System.Data.Sql;
using System.Collections.Specialized;
using System.IO;
using Microsoft.SqlServer.Server;

namespace StoredProcReplication
{

	public class SelfCreatingStoredProcedure : IDisposable
	{
		static HashSet<string> procsOnDisk = new HashSet<string>();

		private SqlCommand _command;
		private SqlConnection _connection;

		public SelfCreatingStoredProcedure(string procName, SqlConnection connection)
		{
			if (procName.IndexOf(".") >= 0)
			{
				procName = procName.Substring(procName.IndexOf('.') + 1);
			}

			_connection = (SqlConnection)(connection as ICloneable).Clone();
			_connection.Open();

			this._command = new SqlCommand(procName, _connection);
			this._command.CommandType = CommandType.StoredProcedure;

			EnsureProc();
		}

		public void AddParameter(SqlParameter parameter)
		{
			this._command.Parameters.Add(parameter);
		}

		public void SetParameterDirection(string key, ParameterDirection dir)
		{
			this._command.Parameters[key].Direction = dir;
		}

		public void SetParameterValue(string key, object value)
		{
			this._command.Parameters[key].Value = value;
		}

		public object GetParameterValue(string key)
		{
			return this._command.Parameters[key].Value;
		}

		public object ExecuteScalar()
		{
			return this._command.ExecuteScalar();
		}

		public int ExecuteNonQuery()
		{
			return this._command.ExecuteNonQuery();
		}

		public SqlDataReader ExecuteReader()
		{
			return this._command.ExecuteReader();
		}

		public SqlDataReader ExecuteSingleRow()
		{
			return this._command.ExecuteReader(CommandBehavior.SingleRow);
		}


		private void EnsureProc()
		{
			if (!procsOnDisk.Contains(this._command.CommandText))
			{
				DateTime dbModifyDate = new DateTime();
				string procText = null;

				using (SqlConnection sqlConnection = (SqlConnection)(this._command.Connection as ICloneable).Clone())
				{
					sqlConnection.Open();
					var command = new SqlCommand("select name,modify_date,OBJECT_DEFINITION(object_id) as text from sys.procedures", sqlConnection);

					SqlDataReader dataReader = command.ExecuteReader();
					bool procExists = false;
					
					while (dataReader.Read())
					{
						if (dataReader["name"].ToString() == this._command.CommandText)
						{
							procText = dataReader["text"].ToString();
							procExists = true;
							dbModifyDate = DateTime.Parse(dataReader["modify_date"].ToString());
							break;
						}
					}

					if (!procExists)
					{
						throw new Exception("Stored proc " + this._command.CommandText + " does not exist in database " + this._command.Connection.Database);
					}

					procsOnDisk.Add(this._command.CommandText);
				}

				//check if file exists
				string filePath = Path.Combine(Environment.CurrentDirectory, this._command.CommandText + ".sql");
				bool fileExists = File.Exists(filePath);

				//write to disk if doesn't exist
				if (!fileExists)
				{
					writeToDisk(filePath, procText);
					return;
				}

				//if file exists ...
				var fileModifyDate = getFileModifyDate(filePath);

				//overwrite file on disk if its modify date is older than the modify date in database
				if (fileModifyDate < dbModifyDate)
				{
					writeToDisk(filePath, procText);
					return;
				}
			}
		}

		private void writeToDisk(string filePath,string procText)
		{
			using (var fStream = File.Open(filePath, FileMode.Create))
			{
				byte[] bytes = new byte[procText.Length * sizeof(char)];
				System.Buffer.BlockCopy(procText.ToCharArray(), 0, bytes, 0, bytes.Length);
				fStream.Write(bytes, 0, bytes.Length);
			}
		}

		private DateTime getFileModifyDate(string filePath)
		{
			try
			{
				DateTime creationTime = File.GetCreationTime(filePath);
				return creationTime;
			}
			catch
			{
				return new DateTime(1, 1, 1); //return oldest date if date can not be retrieved from file
			}
		}

		public void Dispose()
		{
			_command.Connection.Close();
		}
	}
}
